<?php
/**
 * @todo: 供应商库存查询
 * @author chamlinsun@qq.com on 2017-11-23
 */

class Controller_shortage extends Controller_Admin{

    public function before(){
        try {
            ACL::Required('shortage report');
        } catch ( HTTP_Exception_403 $e ) {
            Message::error('sorry，你没有权限进这个目录');
            $this->request->redirect ( URL::base (true) );
        }
        parent::before();
    }

    public function action_index(){
        $this->title = __("供应商库存查询");
        
        $export = $_GET['export'] ? 1 : 0;
        if(!$export) {
            $count = self::getCount($_GET);
            $per_page = 10;
            $pagination = Pagination::factory(array(
                'items_per_page' => $per_page,
                'total_items' => $count,
                'uri' => $this->request->uri(),
            ));
            $current_page = isset($_GET['page'])?$_GET['page']:1;
            $start_page = ($current_page==0)?0:($current_page-1)*$per_page;
            $limitsql = " LIMIT ".($start_page).",".$per_page;
            $data = $this->getData($_GET, $limitsql);
        } else {
            $columns = array(
                "sku" => "SKU",
                "warehouseid" => "仓库",
                "supplier_name" => "供应商",
                "enterprise_dominant" => "主体",
                "productname" => "中文名称",
                "nuit" => "规格",
                "position" => "储位",
                "begin_qty" => "期初",
                "begin_amount" => "期初成本",
                "in_qty" => "期入",
                "in_amount" => "期入成本",
                "out_qty" => "期出",
                "out_amount" => "期出成本",
                "end_qty" => "期末结存",
                "end_amount" => "期末成本",
            );
            $data = $this->getData($_GET);
            $this->exportCsv($data, $columns);
        }
        $companies = Kohana::$config->load('enterprise_dominant')->as_array();
        $warehouses = Kohana::$config->load('warehouse')->warehouses;
        
        $view = View::factory('shortage');
        $view->set('pagination',$pagination);
        $view->set('page',isset($page) ? $page : '');
        $view->set('get',$_GET);
        $view->set('data',$data);
        $view->set('companies', $companies);
        $view->set('warehouses', $warehouses);
        $this->response->body($view);
    }

    /**
     * 待收货的预约单报表下载
     * chamlinsun@qq.com 2017-11-30
     */
    public function Action_export(){
        $result_all  = $this->getData($_GET);
        $resultNames = array(
            "sku" => "SKU",
            "supplier_name" => "供应商",
            "body" => "主体",
            "productname" => "中文名称",
            "nuit" => "规格",
            "s_position" => "储位",
            "begin_qty" => "期初",
            "in_qty" => "期入",
            "out_qty" => "期出",
            "end_qty" => "期末结存",
        );
        $content     = $this->createXLSTable($resultNames,$result_all);
        header("Content-type:application/vnd.ms-execl;charset=gb2312");
        header("Content-Disposition:attactment;filename=供应商库存查询的导出数据.xls");
        header("Pragma: no-cache");
        header("Expires: 0");
        echo $content;
        exit;
    }
    
    /**
     * 获取前台提交数据进行相关数据查询的操作
     * @author chamlinsun@qq.com 2017-11-24
     */
    static protected function getData($condition, $limit = ''){
        $data = array();
        $sku = isset($condition['sku']) ? $condition['sku'] : '';
        if (isset($condition['supplier_name']) && !empty($condition['supplier_name'])) {
            $sql = "SELECT supplier_id FROM vmi_supplier_skus WHERE supplier_name='" . addslashes(trim($condition['supplier_name'])) . "' AND isavailable=1 LIMIT 1";
            $supplier_id = DB::query(Database::SELECT,$sql)->execute('newerp')->get('supplier_id');
        }
        if($condition['supplier_id']) {
            $supplier_id = intval($condition['supplier_id']);
        }
        $warehouseid = isset($condition['warehouseid']) ? intval($condition['warehouseid']) : 0;
        $enterprise_dominant = isset($condition['enterprise_dominant']) ? intval($condition['enterprise_dominant']) : 0;
        $thismonth = date('Y-m-01');
        $sql = "SELECT t1.supplier_id,t1.supplier_name,t1.sku,t1.productname,t1.nuit,t1.price,
            t2.stock AS begin_qty,t2.amount AS begin_amount,
            (CASE WHEN t2.warehouseid IS NULL THEN t3.warehouseid ELSE t2.warehouseid END) AS warehouseid,
            (CASE WHEN t2.enterprise_dominant IS NULL THEN t3.enterprise_dominant ELSE t2.enterprise_dominant END) AS enterprise_dominant
            FROM vmi_supplier_skus t1 
            LEFT JOIN sku_stocks t2 ON t1.sku=t2.sku AND t1.supplier_id=t2.supplier_id AND t2.date='{$thismonth}'
            LEFT JOIN warehouseorders t3 ON t3.sku=t1.sku AND t3.supplier_id=t1.supplier_id AND warehouse_date>='{$thismonth} 00:00:00'
            WHERE 1=1";
        $sku && ($sql .= " AND t1.sku='{$sku}'");
        $supplier_id && ($sql .= " AND t1.supplier_id={$supplier_id}");
        $sql .= " GROUP BY sku,supplier_id,warehouseid,enterprise_dominant";
        $where = array();
        if($warehouseid) {
            $where[] = "warehouseid={$warehouseid}";
        }
        if($enterprise_dominant) {
            $where[] = "enterprise_dominant={$enterprise_dominant}";
        }
        if(isset($where[0])) {
            $sql .= " HAVING " . implode(' AND ', $where);
            $where = ' AND ' . implode(' AND ', $where);
        } else {
            $where = '';
        }
        // echo $sql;exit;
        $limit && ($sql .= $limit);
        $rows = DB::query(Database::SELECT, $sql)->execute('newerp')->as_array();
        if(empty($rows)) {
            return $data;
        }
        $skus = $suppliers = array();
        foreach($rows as $row) {
            $key = $row['sku'] . '_' . $row['supplier_id'];
            if($row['warehouseid']) {
                $key .= '_' . $row['warehouseid'] . '_' . $row['enterprise_dominant'];
            }
            $data[$key] = $row;
            $skus[] = $row['sku'];
            $suppliers[] = (int)$row['supplier_id'];
        }
        $sku_str = "'" . implode("','", $skus) . "'";
        $supplier_str = implode(",", $suppliers);
        $sql_1 = "SELECT sku,warehouseid,supplier_id,enterprise_dominant,SUM(warehouse_quantity) AS qty,SUM(cost) AS cost,storage_position FROM warehouseorders
            WHERE sku IN ($sku_str) AND supplier_id IN ($supplier_str) AND warehouse_date>='{$thismonth} 00:00:00' {$where}
            GROUP BY sku,warehouseid,supplier_id,enterprise_dominant";
        $rows = DB::query(Database::SELECT, $sql_1)->execute('newerp')->as_array();
        if(!empty($rows)) {
            foreach($rows as $row) {
                $key = $row['sku'] . '_' . $row['supplier_id'] . '_' . $row['warehouseid'] . '_' . $row['enterprise_dominant'];
                if(!isset($data[$key])){
                    if(isset($data[$row['sku'] . '_' . $row['supplier_id']])) {
                        $data[$key] = $data[$row['sku'] . '_' . $row['supplier_id']];
                        $data[$key]['warehouseid'] = $row['warehouseid'];
                        $data[$key]['enterprise_dominant'] = $row['enterprise_dominant'];
                        unset($data[$row['sku'] . '_' . $row['supplier_id']]);
                    }else{
                        continue;
                    }
                }
                $data[$key]['in_qty'] = $row['qty'];
                $data[$key]['in_amount'] = $row['cost'];
                $data[$key]['position'] = $row['storage_position'];
            }
        }
        $sql_2 = "SELECT sku,warehouseid,supplier_id,enterprise_dominant,SUM(quantity) AS qty,SUM(cost) AS cost,storage_position FROM deliveryorders
            WHERE sku IN ($sku_str) AND supplier_id IN ($supplier_str) AND delivery_date>='{$thismonth} 00:00:00' {$where}
            GROUP BY sku,warehouseid,supplier_id,enterprise_dominant";
        $rows = DB::query(Database::SELECT, $sql_2)->execute('newerp')->as_array();
        if(!empty($rows)) {
            foreach($rows as $row) {
                $key = $row['sku'] . '_' . $row['supplier_id'] . '_' . $row['warehouseid'] . '_' . $row['enterprise_dominant'];
                if(!isset($data[$key])) {
                    if(isset($data[$row['sku'] . '_' . $row['supplier_id']])) {
                        $data[$key] = $data[$row['sku'] . '_' . $row['supplier_id']];
                        $data[$key]['warehouseid'] = $row['warehouseid'];
                        $data[$key]['enterprise_dominant'] = $row['enterprise_dominant'];
                        unset($data[$row['sku'] . '_' . $row['supplier_id']]);
                    }else{
                        continue;
                    }
                }
                $data[$key]['out_qty'] = $row['qty'];
                $data[$key]['out_amount'] = $row['cost'];
                $data[$key]['position'] || ($data[$key]['position'] = $row['storage_position']);
            }
        }
        foreach($data as $key => &$row) {
            isset($row['begin_qty']) || $row['begin_qty'] = 0;
            isset($row['begin_amount']) || $row['begin_amount'] = 0;
            isset($row['in_qty']) || $row['in_qty'] = 0;
            isset($row['in_amount']) || $row['in_amount'] = 0;
            isset($row['out_qty']) || $row['out_qty'] = 0;
            isset($row['out_amount']) || $row['out_amount'] = 0;
            $row['end_qty'] = $row['begin_qty'] + $row['in_qty'] - $row['out_qty'];
            $row['end_amount'] = $row['end_qty'] * $row['price'];
        }
        return $data;
    }
    
    /**
     *根据列明和已获得的相关信息数组生成table类型的字符串输出，用于后面进行excel表格生成
     *NIRUI 2016-11-04
     */
    static protected function createXLSTable($columnNames, $datas){
        $companies = Kohana::$config->load('enterprise_dominant')->as_array();
        $warehouses = Kohana::$config->load('warehouse')->warehouses;
        header("Content-type:application/vnd.ms-execl;charset=gb2312");
        header("Content-Disposition:attactment;filename=供应商库存查询的导出数据.xls");
        header("Pragma: no-cache");
        header("Expires: 0");
        $keys    = array_keys($columnNames);
        $content = "";
        $content .= "<table border='1'><tr>";
        foreach($columnNames as $key => $columnName){
            $content .= "<td>$columnName</td>";
        }
        $content .= "</tr>";
        $companies[0] = '无主体';
        foreach($datas as $key => $data){
            $data['warehouseid'] = $warehouses[$data['warehouseid']]['name'];
            $data['enterprise_dominant'] = $companies[$data['enterprise_dominant']];
            $content .= "<tr>";
            foreach($keys as $key){
                $content .= "<td>".$data[$key]."</td>";
            }
            $content .= "</tr>";
        }
        $content .= "</table>";
        echo $content;
        exit;
    }
    
    // 供应商动销查询导出csv
    protected function exportCsv($data, $columns){
        ini_set('max_execution_time', '0');
        ini_set('memory_limit', '2048M');
        $filename = 'VMI供应商库存_'.date('Ymd').'.csv';
        $companies = Kohana::$config->load('enterprise_dominant')->as_array();
        $warehouses = Kohana::$config->load('warehouse')->warehouses;
        $titles = array_values($columns);
        $content = implode(',', $titles) . "\r\n";
        foreach($data as $v) {
            $v['warehouseid'] = $warehouses[$v['warehouseid']]['name'];
            $v['enterprise_dominant'] = $companies[$v['enterprise_dominant']];
            $row = array();
            foreach($columns as $key => $var) {
                $row[] = '"' . $v[$key] . '"';
            }
            $content .= implode(',', $row) . "\r\n";
        }
        header("Content-type:text/csv"); 
        header("Content-Disposition:attachment;filename=".$filename); 
        header('Cache-Control:must-revalidate,post-check=0,pre-check=0'); 
        header('Expires:0'); 
        header('Pragma:public');
        echo $content;exit;
    }
    
    /**
     * @param $condition 筛选条件
     * @return mixed 返回符合筛选条件的记录数量
     * 计数方法，统计符合筛选条件的记录条数
     */
    protected static function getCount($condition) {
        $data = array();
        $sku = isset($condition['sku']) ? $condition['sku'] : '';
        if (isset($condition['supplier_name']) && !empty($condition['supplier_name'])) {
            $sql = "SELECT supplier_id FROM vmi_supplier_skus WHERE supplier_name='" . addslashes(trim($condition['supplier_name'])) . "' AND isavailable=1 LIMIT 1";
            $supplier_id = DB::query(Database::SELECT,$sql)->execute('newerp')->get('supplier_id');
        }
        if($condition['supplier_id']) {
            $supplier_id = intval($condition['supplier_id']);
        }
        $warehouseid = isset($condition['warehouseid']) ? intval($condition['warehouseid']) : 0;
        $enterprise_dominant = isset($condition['enterprise_dominant']) ? intval($condition['enterprise_dominant']) : 0;
        $thismonth = date('Y-m-01');
        $sql = "SELECT t1.supplier_id,t1.supplier_name,t1.sku,t1.productname,t1.nuit,t1.price,
            t2.stock AS begin_qty,t2.amount AS begin_amount,
            (CASE WHEN t2.warehouseid IS NULL THEN t3.warehouseid ELSE t2.warehouseid END) AS warehouseid,
            (CASE WHEN t2.enterprise_dominant IS NULL THEN t3.enterprise_dominant ELSE t2.enterprise_dominant END) AS enterprise_dominant
            FROM vmi_supplier_skus t1 
            LEFT JOIN sku_stocks t2 ON t1.sku=t2.sku AND t1.supplier_id=t2.supplier_id AND t2.date='{$thismonth}'
            LEFT JOIN warehouseorders t3 ON t3.sku=t1.sku AND t3.supplier_id=t1.supplier_id AND warehouse_date>='{$thismonth} 00:00:00'
            WHERE 1=1";
        $sku && ($sql .= " AND t1.sku='{$sku}'");
        $supplier_id && ($sql .= " AND t1.supplier_id={$supplier_id}");
        $sql .= " GROUP BY sku,supplier_id,warehouseid,enterprise_dominant";
        $where = array();
        if($warehouseid) {
            $where[] = "warehouseid={$warehouseid}";
        }
        if($enterprise_dominant) {
            $where[] = "enterprise_dominant={$enterprise_dominant}";
        }
        if(isset($where[0])) {
            $sql .= " HAVING " . implode(' AND ', $where);
        }
        $sql_count = "SELECT COUNT(*) AS total FROM ($sql) tmp";
        return DB::query(Database::SELECT, $sql_count)->execute('newerp')->get('total');
    }
}